import { connection } from '../app/database/mysql';
import { getIdCountry } from '../category/category.service';
import { actorModel } from './actor.model';

/**
 * 获取内容
 */
export const getActors = async (pageNo: number, pageSize: number, sex: number, nationality: number, keyWord: string) => {
  // 准备查询
  let statement = `
    SELECT SQL_CALC_FOUND_ROWS *, COUNT(id) OVER () AS total FROM actors
  `;
  let where = []
  if (nationality >= 0) {
    where.push(`nationality = ` + nationality)
  }
  if (sex >= 0) {
    where.push(`sex = ` + sex)
  }
  if (keyWord != '') {
    where.push(`name LIKE '${keyWord}%'`)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  };
  statement += ` ORDER by id DESC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalStatement = `SELECT COUNT(id) as total FROM actors`
  const [result] = await connection.promise().query(totalStatement)
  let totalCount: any;
  if (data[0] && data[0].total) totalCount = data[0].total;
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize

  // 添加国籍
  for(let item of data as any) {
    item.country = await getIdCountry(parseInt(item.nationality, 10));;
  }

  // 提供数据
  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;
};

/**
 * 按 ID 获取内容
 */
export const getByIdActor = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM actors
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  let moviesIds = [];
  if (data[0].movies !== '') moviesIds.push(data[0].movies)
  let moviesList = [];

  if (moviesIds.length > 0) {
    for (let i = 0; i < moviesIds.length; i++) {
      let numberStr = moviesIds[i].split(','); // 将字符串数字转换为整数
      
      for (let j = 0; j < numberStr.length; j++) {
        let number = parseInt(numberStr[j], 10); // 解析字符串为十进制数字
        const movie = await getByMovie(number)
        moviesList.push(movie[0]);
      }
      
    }
  }

  for (let item of data as any) {
    item.nationality = await getIdCountry(parseInt(item.nationality, 10));
    item.moviesList = moviesList;
  }

  // 提供数据
  return data;
}

/**
 * 按 ID 单独获取演员信息
 */
export const getActorsById = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM actors
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
};


/**
 * 创建内容
 */
export const createActor = async (post: actorModel) => {
  // 准备查询
  const statement = `
    INSERT INTO actors
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 删除内容
 */
export const deleteActor = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM actors
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 更新内容
 */
export const updateActor = async (post: actorModel, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE actors
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}






// 获取主角电影
const getByMovie = async (id: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM movies
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, id);

  // 提供数据
  return data;
}
